﻿/*
Cập nhật CSDL ngày 16-09-2013
*/
/*================================
2. tblFile: + Rename khóa PK_tblChuyenHang thành PK_tblFile
*/
EXEC sp_rename 'dbo.tblFile.PK_tblChuyenHang', 'PK_tblFile'
/*================================
3. Tạo quan hệ giữa tblUser và tblFile
*/
go
ALTER TABLE [dbo].[tblFile]  WITH CHECK ADD  CONSTRAINT [FK_tblFile_tblUser] FOREIGN KEY([ID_User])
REFERENCES [dbo].[tblUser] ([ID_User])

/*================================
4. Sửa tên ràng buộc khóa chính PK_tblKhachHang thành PK_tblCompany
*/
go
exec sp_rename 'tblCompany.PK_tblKhachHang', 'PK_tblCompany'

/*================================
5.	Sửa tên khóa chính ID_FeeCont thành ID_ContFee
	Sửa tên ràng buộc khóa chính PK_tblFeeCont thành PK_tblContFee
*/
go
EXEC sp_rename 'dbo.tblContFee.ID_FeeCont', 'ID_ContFee', 'COLUMN'
go
exec sp_rename 'tblContFee.PK_tblFeeCont', 'PK_tblContFee'

/*================================
6.	Thêm IsExport để phân loại khách hàng hàng nhập với hàng xuất
*/
go
ALTER TABLE [dbo].[tblCompany]
Add [IsExport] [bit] NULL

/*================================
7.	Thêm IsExport để phân loại ForeignCompany hàng nhập với hàng xuất
*/
go
ALTER TABLE [dbo].[tblForeignCompany]
Add [IsExport] [bit] NULL

/*================================
8.	Rename tblCustomer_ForeignCP thành tblCompany_ForeignCP
*/
go
exec sp_rename 'tblCustomer_ForeignCP', 'tblCompany_ForeignCP'
go
exec sp_rename 'PK_tblCustomer_ForeignCP', 'PK_tblCompany_ForeignCP'
go
exec sp_rename 'FK_tblCustomer_ForeignCP_tlbForeignCompany', 'FK_tblCompany_ForeignCP_tlbForeignCompany'
go
exec sp_rename 'FK_tblCustomer_ForeignCP_tblCompany', 'FK_tblCompany_ForeignCP_tblCompany'


/*================================
9.	Cập nhật ràng buộc quan hệ với bảng tblFileMBL: 
*/
go
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFileMBL_tblFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFileMBL]'))
ALTER TABLE [dbo].[tblFileMBL] DROP CONSTRAINT [FK_tblFileMBL_tblFile]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFileMBL_tblMBL]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFileMBL]'))
ALTER TABLE [dbo].[tblFileMBL] DROP CONSTRAINT [FK_tblFileMBL_tblMBL]
GO

ALTER TABLE [dbo].[tblFileMBL]  WITH CHECK ADD  CONSTRAINT [FK_tblFileMBL_tblFile] FOREIGN KEY([ID_File])
REFERENCES [dbo].[tblFile] ([ID_File])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblFileMBL] CHECK CONSTRAINT [FK_tblFileMBL_tblFile]
GO

ALTER TABLE [dbo].[tblFileMBL]  WITH CHECK ADD  CONSTRAINT [FK_tblFileMBL_tblMBL] FOREIGN KEY([ID_MBL])
REFERENCES [dbo].[tblMBL] ([ID_MBL])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblFileMBL] CHECK CONSTRAINT [FK_tblFileMBL_tblMBL]


/*================================
10.	Cập nhật ràng buộc quan hệ với bảng tblHBL: 
*/
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblHBL_tblFileMBL]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblHBL]'))
ALTER TABLE [dbo].[tblHBL] DROP CONSTRAINT [FK_tblHBL_tblFileMBL]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblHBL_tblTaxReturn]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblHBL]'))
ALTER TABLE [dbo].[tblHBL] DROP CONSTRAINT [FK_tblHBL_tblTaxReturn]
GO

ALTER TABLE [dbo].[tblHBL]  WITH CHECK ADD  CONSTRAINT [FK_tblHBL_tblFileMBL] FOREIGN KEY([ID_FileMBL])
REFERENCES [dbo].[tblFileMBL] ([ID_FileMBL])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblHBL] CHECK CONSTRAINT [FK_tblHBL_tblFileMBL]
GO

ALTER TABLE [dbo].[tblHBL]  WITH CHECK ADD  CONSTRAINT [FK_tblHBL_tblTaxReturn] FOREIGN KEY([ID_TaxReturn])
REFERENCES [dbo].[tblTaxReturn] ([ID_TaxReturn])
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[tblHBL] CHECK CONSTRAINT [FK_tblHBL_tblTaxReturn]



/*================================
11.	Cập nhật ràng buộc quan hệ với bảng tblCont
*/
go
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCont_tblBooking]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCont]'))
ALTER TABLE [dbo].[tblCont] DROP CONSTRAINT [FK_tblCont_tblBooking]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCont_tblContType]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCont]'))
ALTER TABLE [dbo].[tblCont] DROP CONSTRAINT [FK_tblCont_tblContType]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCont_tblDemDet]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCont]'))
ALTER TABLE [dbo].[tblCont] DROP CONSTRAINT [FK_tblCont_tblDemDet]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCont_tblHBL]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCont]'))
ALTER TABLE [dbo].[tblCont] DROP CONSTRAINT [FK_tblCont_tblHBL]
GO

ALTER TABLE [dbo].[tblCont]  WITH CHECK ADD  CONSTRAINT [FK_tblCont_tblBooking] FOREIGN KEY([ID_Booking])
REFERENCES [dbo].[tblBooking] ([ID_Booking])
GO

ALTER TABLE [dbo].[tblCont] CHECK CONSTRAINT [FK_tblCont_tblBooking]
GO

ALTER TABLE [dbo].[tblCont]  WITH CHECK ADD  CONSTRAINT [FK_tblCont_tblContType] FOREIGN KEY([ID_ContType])
REFERENCES [dbo].[tblContType] ([ID_ContType])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[tblCont] CHECK CONSTRAINT [FK_tblCont_tblContType]
GO

ALTER TABLE [dbo].[tblCont]  WITH CHECK ADD  CONSTRAINT [FK_tblCont_tblDemDet] FOREIGN KEY([ID_DemDet])
REFERENCES [dbo].[tblDemDet] ([ID_DemDet])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[tblCont] CHECK CONSTRAINT [FK_tblCont_tblDemDet]
GO

ALTER TABLE [dbo].[tblCont]  WITH CHECK ADD  CONSTRAINT [FK_tblCont_tblHBL] FOREIGN KEY([ID_HBL])
REFERENCES [dbo].[tblHBL] ([ID_HBL])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[tblCont] CHECK CONSTRAINT [FK_tblCont_tblHBL]


/*================================
12.	Cập nhật ràng buộc quan hệ với bảng tblCompany_ForeignCP
*/
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCompany_ForeignCP_tblCompany]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCompany_ForeignCP]'))
ALTER TABLE [dbo].[tblCompany_ForeignCP] DROP CONSTRAINT [FK_tblCompany_ForeignCP_tblCompany]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCompany_ForeignCP_tblForeignCompany]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCompany_ForeignCP]'))
ALTER TABLE [dbo].[tblCompany_ForeignCP] DROP CONSTRAINT [FK_tblCompany_ForeignCP_tblForeignCompany]
GO

ALTER TABLE [dbo].[tblCompany_ForeignCP]  WITH CHECK ADD  CONSTRAINT [FK_tblCompany_ForeignCP_tblCompany] FOREIGN KEY([ID_Company])
REFERENCES [dbo].[tblCompany] ([ID_Company])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblCompany_ForeignCP] CHECK CONSTRAINT [FK_tblCompany_ForeignCP_tblCompany]
GO

ALTER TABLE [dbo].[tblCompany_ForeignCP]  WITH CHECK ADD  CONSTRAINT [FK_tblCompany_ForeignCP_tblForeignCompany] FOREIGN KEY([ID_ForeignCompany])
REFERENCES [dbo].[tblForeignCompany] ([ID_ForeignCompany])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblCompany_ForeignCP] CHECK CONSTRAINT [FK_tblCompany_ForeignCP_tblForeignCompany]



